package com.example.fw;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.example.tests.ContactData;
import com.example.tests.GroupData;
import com.example.utils.SortedListOf;

public class JdbcHelper {

	private Connection conn;
	
	public JdbcHelper(ApplicationManager app, String url) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		try {
			conn = DriverManager.getConnection(url);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public SortedListOf<GroupData> listGroups() {
	  SortedListOf<GroupData> groups = new SortedListOf<GroupData>();
		Statement st = null;
		ResultSet res = null;
		
		try {
			st = conn.createStatement();
			res = st.executeQuery("SELECT group_name,group_header,group_footer FROM group_list");
			while (res.next()) {
				GroupData gr = new GroupData().withName(res.getString(1)).withHeader(res.getString(2)).withFooter(res.getString(3));
				groups.add(gr);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				res.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return groups;
	}
	
	public SortedListOf<ContactData> listContacts() {
		  SortedListOf<ContactData> contacts = new SortedListOf<ContactData>();
			Statement st = null;
			ResultSet res = null;
			
			try {
				st = conn.createStatement();
				res = st.executeQuery("SELECT id,firstname,lastname,address,home,mobile,work,email,email2,bday,bmonth,byear,address2,phone2 FROM addressbook");
				
				while (res.next()) {
					ContactData ct = new ContactData().withId(res.getString(1))
                            .withFirstname(res.getString(2))
                            .withLastname(res.getString(3))
                            .withAddress(res.getString(4))
                            .withHometel(res.getString(5))
                            .withMobiltel(res.getString(6))
                            .withWorktel(res.getString(7))
                            .withEmail(res.getString(8))
                            .withEmail2(res.getString(9))
                            .withBday(res.getString(10))
                            .withBmohth(res.getString(11))
                            .withByear(res.getString(12))
                            .withAddressdop(res.getString(13))
                            .withPhone2(res.getString(14));
					contacts.add(ct);
				}

			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					res.close();
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			return contacts;
		}
}
